<?php defined('SYSPATH') or die('No direct access allowed.');

class Model_User_Profile extends ORM {

	// Relationships
	protected $_belongs_to = array('user' => array(),
                                       'photo' => array('model' => 'file','foreign_key' => 'file_id'),
                                       'country' => array('model' => 'country', 'foreign_key' => 'country_iso'));
         
        
        // Rules
        public function rules()
	{
		return array(
			'name' => array(
				array('not_empty'),
				array('max_length', array(':value', 127)),
			),
                        'surname' => array(
				array('not_empty'),
				array('max_length', array(':value', 127)),                        
			),
                        'description' => array(
				array('not_empty'),
				array('max_length', array(':value', 127)),                       
			),
                        'address' => array(
				array('not_empty'),
				array('max_length', array(':value', 255)),                        
			),
                        'birth_date' => array(
				array('not_empty'),
				array('date'),                      
			),
                        'country_iso' => array(
                                array('not_empty'),
                        )
                        
		);
	}
        
        public function filters()
	{
		return array(
			'name' => array(
                                //xss clean
				array(array('Security', 'xss_clean')),
                                array('trim')
			),
                        'surname' => array(
                                array(array('Security', 'xss_clean')),
                                array('trim')
                        ),
                        'description' => array(
				array(array('Security', 'xss_clean')),
                                array('trim')
			),
                        'address' => array(
                                array(array('Security', 'xss_clean')),
                                array('trim')
                        ),
                        'birth_date' => array(
				array(array('Security', 'xss_clean'))
			),
                        'url' => array(
                                array(array($this, 'unique_url'), array(':value'))
                        )
                       
		);
	}

	/**
	 * Labels for fields in this model
	 *
	 * @return array Labels
	 */
	public function labels()
	{
		return array(
			'name'        => __('Name'),
			'surname'     => __('Surname'),
			'description' => __('My description'),
                        'address'     => __('Address'),
			'birth_date'  => __('Date of birth'),
                        'country_iso'  => __('Country')
		);
	}
        
        public function unique_url($value,$suffix = null)
	{
                $_value = $value.(string)$suffix;
		$is_unique = !(bool) DB::select(array('COUNT("*")', 'total_count'))
                                    ->from($this->_table_name)
                                    ->where('url', '=', $_value)
                                    ->where($this->_primary_key, '!=', $this->pk())
                                    ->execute($this->_db)
                                    ->get('total_count');
                
                if ($is_unique)
                {
                    return $_value;
                }
                else
                {
                    $suffix = (int)$suffix + 1;
                    $this->unique_url($value,$suffix);
                }
	}
        
        /* data structure : ['search_word','location','company','current','school','field','joined']*/
        public function get_profiles(array $data)
        {
/* start old version
            $temp_profiles = $this->select(array('user_profiles.file_id','photo'),
                                           array('files.id','file_id'),
                                           array('files.name','file_name'),
                                           array('files.extension','file_extension'))
                                  ->join('files','LEFT')->on('files.id', '=', 'user_profiles.file_id')
                                  ->where(DB::expr("CONCAT(`user_profiles`.`name`, ' ', `user_profiles`.`surname`)"), 'like', $data['search_word'].'%');
            
            if (isset($data['limit']) AND $data['limit'])
            {
                $temp_profiles->limit((int) $limit);
            }
            
            if (isset($data['offset']) AND $data['offset'])
            {
                $temp_profiles->offset((int) $offset);
            }
            
            if ($data['country'])
            {
                $temp_profiles->where('country_iso', '=', $data['country']);
            }
            
            if ($data['location'])
            {
               $temp_profiles->where('address', 'like', $data['location'].'%');
            }              

            if ($data['company'])
            {
               $temp_profiles->join('user_experiences')->on('user_profiles.user_id','=','user_experiences.user_id')
                             ->where('user_experiences.employer', 'like', $data['company'].'%');

               if (array_key_exists('current', $data) AND $data['current'] != '')
               {
                    $temp_profiles->where('user_experiences.date_end', '=', null);
               }
            }

            if ($data['school'])
            {
               $temp_profiles->join('user_educations')->on('user_profiles.user_id','=','user_educations.user_id')
                             ->where('user_educations.school', 'like', $data['school'].'%');
            }

            if ($data['field'])
            {
                $temp_profiles->join('fields_users')->on('user_profiles.user_id','=','fields_users.user_id')
                              ->where('fields_users.field_id', '=', $data['field']);    
            }

            if ($data['joined'])
            {
                switch($data['joined'])
                {
                    case '2':
                        $temp_profiles->join('users')->on('user_profiles.user_id','=','users.id')
                                      ->where('users.date_created', '>', date('Y-m-d 00:00:00', strtotime ("-1 day")));
                        break;

                    case '3':
                        $temp_profiles->join('users')->on('user_profiles.user_id','=','users.id')
                                      ->where('users.date_created', '>', date('Y-m-d 00:00:00', strtotime ("-1 week")));
                        break;

                    case '4':
                       $temp_profiles->join('users')->on('user_profiles.user_id','=','users.id')
                                      ->where('users.date_created', '>', date('Y-m-d 00:00:00', strtotime ("-2 weeks")));
                        break;

                    case '5':
                        $temp_profiles->join('users')->on('user_profiles.user_id','=','users.id')
                                      ->where('users.date_created', '>', date('Y-m-d 00:00:00', strtotime ("-1 month")));
                        break;
                    
                     case '6':
                        $temp_profiles->join('users')->on('user_profiles.user_id','=','users.id')
                                      ->where('users.date_created', '>', date('Y-m-d 00:00:00', strtotime ("-3 months")));
                        break;

                    default:
                        break;
                }                
            }

            $temp_profiles = $temp_profiles->find_all();
           
 end old version*/ 
            
            $sphinx = new Sphinx();
            $sphinx->SetServer('127.0.0.1', 3312); 
            $sphinx->SetConnectTimeout(5);
            $sphinx->SetMatchMode(SPH_MATCH_EXTENDED2); 
       
            $query = '';
            if($data['search_word'])
            {
                $query .= '@(username,description) '.$data['search_word'];
            }
                        
            if ($data['country'])
            {
               $query .= '@country_iso '.$data['country'];
            }
            
            if ($data['location'])
            {
               $query .= '@locality '.$data['location'];
            }              

            if ($data['company'])
            {
               $query .= '@companies '.$data['company'];
            }

            if ($data['school'])
            {
               $query .= '@schools '.$data['school'];
            }
            
            if(is_array($data['field']))
            {
                $sphinx->SetFilter('fields', $data['field']);
            } 
            elseif((int) $data['field'])
            {
                 $sphinx->SetFilter('fields', array($data['field']));
            }
            
            if ($data['employed']) 
            {
                $sphinx->SetFilter('is_employed', array(1));
            }
            
            if ($data['not_employed'])
            {
                $sphinx->SetFilter('is_employed', array(0));
            }  
            
            if($data['joined'])
            {
                switch($data['joined'])
                {                
                    case '1': //1 day ago
                        $sphinx->SetFilterRange('date_created', mktime(0, 0, 0), time());
                        break;
                    case '2': //1 week ago
                        $sphinx->SetFilterRange('date_created', strtotime("one week ago"), time());
                        break;
                    case '3': //2 weeks ago
                        $sphinx->SetFilterRange('date_created', strtotime("two weeks ago"), time());
                        break;
                    case '4': //1 month ago
                        $sphinx->SetFilterRange('date_created',strtotime("one month ago"), time());
                        break;
                    case '5': //3months ago
                        $sphinx->SetFilterRange('date_created',strtotime("three months ago"), time());
                        break;
                }
            }
            
            if (isset($data['limit']))
            {             
                $sphinx->SetLimits((int)$data['offset'], (int)$data['limit']);
            }
  
            $result = $sphinx->Query($query, 'i_people');
            if($result === false)
            {
                echo $sphinx->GetLastError();
            }
            else
            {
                if($sphinx->GetLastWarning() != '')
                {
                    echo $sphinx->GetLastWarning(); 
                }
            }
            
            //echo "<pre>"; var_dump($result);echo "</pre>";
            //sphinx end 
      
            $profiles = array();
            //query based od sphinx result
            if(isset($result['matches']))
            {
                 $temp_profiles = DB::select(array('user_profiles.url', 'url'),
                                             array('user_profiles.name', 'name'),
                                             array('user_profiles.surname', 'surname'),
                                             array('user_profiles.description', 'description'),
                                             array('user_profiles.address', 'address'),
                                             array('countries.name', 'country'),  
                                             array('files.id','file_id'),
                                             array('files.name','file_name'),
                                             array('files.extension','file_extension'))
                           ->from('user_profiles')
                           ->join('files','LEFT')->on('files.id', '=', 'user_profiles.file_id')
                           ->join('countries')->on('countries.iso3', '=', 'user_profiles.country_iso')
                           ->where('user_profiles.user_id', 'IN', array_keys($result['matches']))
                           ->order_by(db::expr('FIND_IN_SET(user_profiles.user_id,\''.implode(',', array_keys($result['matches'])).'\')'))
                           ->as_object()
                           ->execute(); 
            
          
                 foreach($temp_profiles as $profile)
                 {                 
                     $profile->photo = Media::get_file_path($profile->file_name, 
                                                            $profile->file_id, 
                                                            $profile->file_extension, 
                                                            true, 
                                                            'mini_square', 
                                                            false);

                     $profiles[] = $profile;
                 }
             }
             return $profiles;
        }
}